package edu.mju.dao.impl;

import edu.mju.dao.IUserDao;
import edu.mju.entity.ScenicSpot;
import edu.mju.entity.User;
import edu.mju.util.Pager;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @Author:wyp
 * @Date:2020/5/9 9:24
 */
public class UserDaoImpl implements IUserDao {
    Connection conn = null;
    PreparedStatement preparedStatement = null ;

    public PreparedStatement preparedStatement(String sql){
        try {
            conn  = MySqlDbUtil.getConnection();
            preparedStatement = conn.prepareStatement(sql);
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("UserDaoImpl>preparedStatement方法出错");
        }
        return preparedStatement;
    }
    @Override
    public User findByUserName(String userName) {
        User user = null;
        ResultSet rs = null;
        String sql = "SELECT * FROM User where userName=?";
        try {
            PreparedStatement pstmt = preparedStatement(sql);
            if(pstmt != null){
                pstmt.setString(1,userName);
                rs = pstmt.executeQuery();
                if(rs.next()){
                    user = new User(rs.getInt("userId"),rs.getString("userName"),
                        rs.getString("userPassword"),rs.getString("userTel"),
                        rs.getString("userSalt"),rs.getByte("userSex"),rs.getString("userRole"));
                }
                rs.close();
                conn.close();
                pstmt.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return user;
    }

    @Override
    public boolean insert(User user) {
        boolean insert= false;
        try {
            String sql = "INSERT INTO User(userName,userPassword,userTel,userSalt,userSex,userRole) VALUES (?,?,?,?,?,?)";
            PreparedStatement pstmt = preparedStatement(sql);
            if(pstmt != null){
                pstmt.setString(1,user.getUserName());
                pstmt.setString(2,user.getUserPassword());
                pstmt.setString(3,user.getUserTel());
                pstmt.setString(4,user.getUsersalt());
                pstmt.setInt(5,user.getUserSex());
                pstmt.setString(6,user.getUserRole());
                int result =pstmt.executeUpdate();
                if(result>0){
                    insert = true;
                }
            }
            pstmt.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return insert;
    }

    /**
     *  执行注册公司用户操作
     * @param user
     * @return 返回User到CompanyComtroller.companyAdd()中
     */
    @Override
    public User insertCompanyUser(User user) {
        try {
            String sql = "INSERT INTO User(userName,userPassword,userTel,userSalt,userRole) VALUES (?,?,?,?,?)";
            PreparedStatement pstmt = preparedStatement(sql);
            if(pstmt != null){
                pstmt.setString(1,user.getUserName());
                pstmt.setString(2,user.getUserPassword());
                pstmt.setString(3,user.getUserTel());
                pstmt.setString(4,user.getUsersalt());
                pstmt.setString(5,"tourCompany");
                int result =pstmt.executeUpdate();
                if(result>0){
                    user = findByUserName(user.getUserName());
                }
            }
            pstmt.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return user;
    }

    @Override
    public boolean update(User user) {
        boolean update = false;
        try {
            String sql = "UPDATE User SET userPassword=?,userTel=?,userSex=? WHERE userName=?";
            PreparedStatement pstmt = preparedStatement(sql);
            pstmt.setString(1,user.getUserPassword());
            pstmt.setString(2,user.getUserTel());
            pstmt.setByte(3,user.getUserSex());
            pstmt.setString(4,user.getUserName());
            int result =pstmt.executeUpdate();
            if(result>0){
                update = true;
            }
            pstmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return update;
    }

    @Override
    public boolean update2(User user) {
        boolean update = false;
        try {
            String sql = "UPDATE User SET userName=?, userPassword=?,userTel=?,userSalt=?,userSex=?,userRole=? WHERE userId=?";
            PreparedStatement pstmt = preparedStatement(sql);
            pstmt.setString(1,user.getUserName());
            pstmt.setString(2,user.getUserPassword());
            pstmt.setString(3,user.getUserTel());
            pstmt.setString(4,user.getUsersalt());
            pstmt.setByte(5,user.getUserSex());
            pstmt.setString(6,user.getUserRole());
            pstmt.setInt(7,user.getUserId());
            int result =pstmt.executeUpdate();
            if(result>0){
                update = true;
            }
            pstmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return update;
    }

    @Override
    public boolean updateFindUser(User user) {
        boolean update = false;
        try {
            String sql = "UPDATE User SET  userPassword=?,userSalt=? WHERE userName=?";
            PreparedStatement pstmt = preparedStatement(sql);
            pstmt.setString(1,user.getUserPassword());
            pstmt.setString(2,user.getUsersalt());
            pstmt.setString(3,user.getUserName());
            int result =pstmt.executeUpdate();
            if(result>0){
                update = true;
            }
            pstmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return update;
    }

    @Override
    public boolean deleteById(Integer userId) {
        boolean delete = false;
        try {
            String sql = "DELETE FROM User WHERE userId=?";
            PreparedStatement pstmt = preparedStatement(sql);
            pstmt.setInt(1,userId);
            int result =pstmt.executeUpdate();
            if(result>0){
                delete = true;
            }
            pstmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return delete;
    }

    @Override
    public boolean delete(User user) {
        boolean delete = false;
        try {
            String sql = "DELETE FROM User WHERE userName=?";
            PreparedStatement pstmt = preparedStatement(sql);
            pstmt.setString(1,user.getUserName());
            int result =pstmt.executeUpdate();
            if(result>0){
                delete = true;
            }
            pstmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return delete;
    }

    @Override
    public User findById(Integer id) {
        User user = new User();
        try {
            Connection conn = MySqlDbUtil.getConnection();
            String sql = "SELECT * FROM User where userId=?";
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,id);
            ResultSet rs = pstmt.executeQuery();
            if(rs.next()){
                user.setUserId(rs.getInt("userId"));
                user.setUserName(rs.getString("userName"));
                user.setUserPassword(rs.getString("userPassword"));
                user.setUserTel(rs.getString("userTel"));
                user.setUsersalt(rs.getString("userSalt"));
                user.setUserSex(rs.getByte("userSex"));
                user.setUserRole(rs.getString("userRole"));
            }
            rs.close();
            pstmt.close();
            conn.close();
        }catch (Exception e){
            e.printStackTrace();
        }
        return user;
    }

    @Override
    public List<User> findAll() {
        return null;
    }

    @Override
    public List<User> find(User user) {
        return null;
    }

    @Override
    public Pager pagenate(int page, int pageSize, User user) {
        Pager pager = new Pager(page,pageSize);
        String sql = "select count(*) as total from User ";
        StringBuilder where = new StringBuilder("where 1=1 ");
        List<Object> params = new ArrayList<>();
        if(user != null) {
            if (user.getUserName() != null && !"".equals(user.getUserName()) && !"null".equals(user.getUserName())) {
                where.append("AND userName like ? ");
                params.add("%" + user.getUserName() + "%");
            }
            if (user.getUserRole() != null && !"".equals(user.getUserRole()) && !"null".equals(user.getUserRole())) {
                where.append("AND userRole like ? ");
                params.add("%" + user.getUserRole() + "%");
            }
        }
        try {
            Connection conn = MySqlDbUtil.getConnection();
            PreparedStatement pstmt = conn.prepareStatement(sql + where.toString());
            for(int i = 0;i<params.size();i++){
                pstmt.setObject(i+1,params.get(i));
            }
            ResultSet rs = pstmt.executeQuery();
            if(rs.next()){
                pager.setRows(rs.getInt("total"));
            }

            rs.close();;
            pstmt.close();
            //这里要注意sql 语句之间的空格
            sql = " select * from User ";
            pstmt = conn.prepareStatement(sql+where.toString()+" limit ?,? ");
            int index = 0;
            for(int i = 0;i<params.size();i++){
                ++index;
                pstmt.setObject(i+1,params.get(i));
            }
            pstmt.setObject(++index,(pager.getPage()-1)*pager.getPageSize());
            pstmt.setInt(++index,pager.getPageSize());
            rs =pstmt.executeQuery();
            List<Object> data = new ArrayList<>();
            while (rs.next()){
                user = new User(rs.getInt("userId"),rs.getString("userName"),
                        rs.getString("userPassword"),rs.getString("userTel"),
                        rs.getString("userSalt"),
                        rs.getByte("userSex"),rs.getString("userRole"));
                data.add(user);
            }
            pager.setData(data);
            rs.close();
            pstmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

        return pager;
    }


}
